<?php
/*
 * SQL Database Backup
 * 用于数据库备份还原
 * 
 * @since 
 *      1.0 2014-9-26 by sutroon
 */
require 'Inc/incDbHead.php';
$q = I('q');
if ($q == 'backup-schema') {
    $str = backup_db_schema($database);
    save_file_dialog('schema-' . date('y-m-d-his') . '.sql', $str);
} elseif ($q == 'backup-data') {
    $str = backup_db_data($database);
    save_file_dialog('data-' . date('y-m-d-his') . '.sql', $str);
}
if ($action == 'showconnection') {
    $r = mysql_query("SELECT DATABASE()") or die(mysql_error());
    die('DB:' . mysql_result($r, 0));
}
?>
<!DOCTYPE html>
<html>
<head>
    <title>SoDatabaseBackup</title>
    <meta charset="UTF-8">
    <style type='text/css'>
        body { font-size: 12px; }
        table { width: 100%; border: solid 1px #CCC; border-left: none; border-bottom: none; }
        ul, li { margin: 0px; list-style: none; padding: 0px; }
        li { line-height: 18px; }
        th:first-child { width: 30px; }
        .th-field { width: 500px; }
        th, td { padding: 5px; border: solid 1px #CCC; border-right: none; border-top: none; }
        tr.tr-alt { background-color: #efefef; }
        a { text-decoration: none; }
        fieldset textarea { width: 100%; }
        .aside { width: 180px; float: left; overflow: hidden; }
        .aside fieldset { margin: 0px 5px 5px 5px; }
        .aside ul { max-height: 300px; width: 160px; overflow: scroll; }
        .aside a { color: #333; }
        .aside .cur { color: #090; font-weight: bold; }
        .main { width: 750px; float: left; border: solid 1px #090; }
        #result-panel { height: 450px; width: 720px; overflow: scroll; }
        #result-panel td { font-size: 11px; }
    </style>
    <script type="text/javascript" src="/Public/Script/jquery-1.11.1.min.js"></script>
</head>
<body>
<?php
// 显示数据库登录表单
if (!$conn_info) {
    echoDbLoginForm();
}
// 全局菜单
include 'Inc/incMenu.php';

echo '<div><a href="?q=backup-schema">[备份结构]</a> <a href="?q=backup-data">[备份数据]</a></div>';

/**
 * 弹出文件下载对话框
 * 注意要放在页面没有html输出的前面,否则会连同html标签一起输出
 * @param $filename 保存的文件名
 * @param $str 内容
 * @since 1.0 2014-9-26 by sutroon
 * @example save_file_dialog('myfile.sql',$content);
 */
function save_file_dialog($filename, $str)
{
    header("Content-Type: application/force-download");
    header('Content-Disposition: attachment; filename="' . $filename . '"');
    die($str);
}

/**
 * 备份整个数据库的结构
 * 包含数据表、视图、存储过程、函数
 * @param $database 数据库名称
 * @return string
 * @since 1.0 2014-9-26 by sutroon
 */
function backup_db_schema($database)
{
    $str = '/**' . PHP_EOL;
    $str .= ' * database: ' . $database . PHP_EOL;
    $str .= ' * created time: ' . date('Y-m-d H:i:s') . PHP_EOL;
    $str .= ' * author: sutroon' . PHP_EOL;
    $str .= ' */' . PHP_EOL;
    // 显示所有数据表
    $result = mysql_query("select TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, `ENGINE`, TABLE_ROWS, DATA_LENGTH, AUTO_INCREMENT, CREATE_TIME, `TABLE_COLLATION`, TABLE_COMMENT  from  information_schema.TABLES where TABLE_TYPE='BASE TABLE' and TABLE_SCHEMA='$database';");
    while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
        $str .= get_create_object_sql($row['TABLE_NAME'], 'TABLE') . ';' . PHP_EOL . PHP_EOL;
    }
    // 显示所有视图
    $result = mysql_query("select TABLE_SCHEMA, TABLE_NAME, VIEW_DEFINITION from information_schema.VIEWS where TABLE_SCHEMA='$database';");
    while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
        $str .= preg_replace('/DEFINER=[^\s]+ /', '', get_create_object_sql($row['TABLE_NAME'], 'VIEW')) . ';' . PHP_EOL . PHP_EOL;
    }
    // 显示所有存储过程
    $result = mysql_query("show procedure status;");
    while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
        if ($row['Db'] == $database) {
            $str .= preg_replace('/DEFINER=[^\s]+ /', '', get_create_object_sql($row['Name'], 'PROCEDURE')) . ';' . PHP_EOL . PHP_EOL;
        }
    }
    // 显示所有函数
    $result = mysql_query("show function status;");
    while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
        if ($row['Db'] == $database) {
            $str .= preg_replace('/DEFINER=[^\s]+ /', '', get_create_object_sql($row['Name'], 'FUNCTION')) . ';' . PHP_EOL . PHP_EOL;
        }
    }
    return $str;
}

/**
 * 备份整个数据库的用户数据
 * @param $database 数据库名称
 * @return string
 * @since 1.0 2014-9-26 by sutroon
 */
function backup_db_data($database, $table = '')
{
    $str = '/**' . PHP_EOL;
    $str .= ' * database: ' . $database . PHP_EOL;
    $str .= ' * created time: ' . date('Y-m-d H:i:s') . PHP_EOL;
    $str .= ' * author: sutroon' . PHP_EOL;
    $str .= ' */' . PHP_EOL;
    $result = mysql_query('show tables;');
    while ($row = mysql_fetch_array($result,MYSQL_NUM)) {
        $str .= backup_table_data($row[0]);
    }
    return $str;
}

/**
 * 备份单个数据表的用户数据
 * @param $table 数据表名称
 * @return string
 * @since 1.0 2014-9-26 by sutroon
 */
function backup_table_data($table)
{
    $str = '';
    $result = mysql_query("select * from `$table`");
    while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
        $keys = array_keys($row);
        $keys = array_map('addslashes', $keys);
        $keys = join('`,`', $keys);
        $keys = "`" . $keys . "`";
        $vals = array_values($row);
        $vals = array_map('addslashes', $vals);
        $vals = join("','", $vals);
        $vals = "'" . $vals . "'";
        $str .= "insert into `$table` ($keys) values ($vals);" . PHP_EOL;
    }
    return $str;
}

?>
</body>
</html>